package com.crm.crm.mapper;

import com.crm.entity.Partner;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author 秋水浮萍
 * @version 1.0.0
 * @ClassName PartnerFind.java
 * @Description TODO
 * @createTime 2021年03月04日 21:09:00
 */
@Repository
public interface CrmPartnerFindMapper {

    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where  crm_partner.disabled=0 and crm_partner.partner_type = #{partnerType}" +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' ",
            "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and crm_partner.create_user_id =#{createId} ",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(asset_info->'$.businessType[*]','\"${typeId}\"')",
            "</if>" +
                    "<if test='belong != null and belong!=\"\"'>" +
                    " and asset_info->'$.belong'=#{belong}",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>" +
                    "  and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>" +
                    "<if test='orgRemark != null and orgRemark!=\"\"'>" +
                    " and crm_partner.org_remark = #{orgRemark}",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>",
            "</if>" +
                    " order BY crm_partner.update_time  DESC" +

                    "</script>"
    })
    @Results(id = "partnerMap",
            value = {
                    @Result(column = "org_type", property = "orgType", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "telephone_list", property = "telephoneList", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "over_area", property = "overArea", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "asset_info", property = "assetInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "fund_info", property = "fundInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "exit_info", property = "exitInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "justice_info", property = "justiceInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "peer_info", property = "peerInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
                    @Result(column = "org_info", property = "orgInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            })
    List<Partner> findAssetInf(String createId, String company, Integer partnerType, String belong, @Param("cities") String[] cities, String orgType, String orgRemark, String typeId);


    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where   crm_partner.disabled=0 and crm_partner.partner_type = #{partnerType}" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and create_user_id =#{createId} ",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(fund_info->'$.fancyList[*].typeId','\"${typeId}\"')" +
                    "</if>" +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' " +
                    "</if>" +

                    "<if test='typeName != null and typeName!=\"\"'>",
            " and json_contains(fund_info->'$.fancyList[*].typeName','\"${typeName}\"')",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>",
            " and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>",
            "<if test='orgRemark != null and orgRemark!=\"\"'>",
            " and crm_partner.org_remark = #{orgRemark}",
            "</if>",
            "<if test='currency != null and currency!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].currency','\"${currency}\"')",
            "</if>",
            "<if test='incomeType != null and incomeType!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].incomeType','\"${incomeType}\"')",
            "</if>",
            "<if test='struct != null and struct!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].struct','\"${struct}\"')",
            "</if>",
            "<if test='scaleMin != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMin',4,1 ) as UNSIGNED INTEGER) &gt;= #{scaleMin}",
            "</if>",
            "<if test='scaleMax != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMax',4,1 ) as UNSIGNED INTEGER) &lt;= #{scaleMax}",
            "</if>",
            "<if test='deadline != null and deadline!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].deadline','\"${deadline}\"')",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    "</if>" +
                    "<if test='incomeRate != null '>" +
                    " and json_contains(fund_info->'$.structList[*].incomeRate', '${incomeRate}')",
            "</if>" +
                    " order BY crm_partner.update_time  DESC",
            "</script>",
    })
    List<Partner> findFundInfo(String createId, String company, Integer partnerType, String orgType, String orgRemark,
                               String currency, String incomeType,
                               String struct, Integer scaleMin, Integer scaleMax, String deadline,
                               @Param("cities") String[] cities, String typeId, String typeName, Integer incomeRate);

    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id " +
                    "where crm_partner.disabled=0 and crm_partner.partner_type = #{partnerType} " +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and create_user_id =#{createId} ",
            "</if>" +
                    "<if test='usage != null and usage!=\"\"'>" +
                    "and  JSON_CONTAINS(exit_info->'$.usage[*]','\"${usage}\"')",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>",
            " and json_contains(exit_info->'$.fancyList[*].typeId','\"${typeId}\"')",
            "</if>",
            "<if test='typeName != null and typeName!=\"\"'>",
            " and json_contains(exit_info->'$.fancyList[*].typeName','\"${typeName}\"')",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>",
            " and  crm_partner.exit_type=#{orgType}",
            "</if>",
            "<if test='company != null and company!=\"\"'>" +
                    " and crm_partner.company like '%${company}%' ",
            "</if>" +
                    "<if test='orgRemark != null and orgRemark!=\"\"'>",
            " and crm_partner.org_remark = #{orgRemark}",
            "</if>",
            "<if test='abilityMin != null and abilityMin!=\"\"'>",
            " and cast(SUBSTRING(exit_info->'$.abilityMin',3,1 ) as UNSIGNED INTEGER) &gt;= #{abilityMin}",
            "</if>",
            "<if test='abilityMax !=null and abilityMax!=\"\"'>",
            " and cast(SUBSTRING(exit_info->'$.abilityMax',3,1 ) as UNSIGNED INTEGER) &lt;= #{abilityMax}",
            "</if>",
            "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    "</if>" +

                    " order BY crm_partner.update_time  DESC",
            "</script>",
    })
    List<Partner> findExitInfo(String createId,String company, Integer partnerType, String orgType, String orgRemark,
                               String usage,
                               String typeId, String typeName, @Param("cities") String[] cities, Integer abilityMin, Integer abilityMax);
    @ResultMap(value = "partnerMap")
    @Select({"<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where crm_partner.disabled=0 and crm_partner.partner_type = #{partnerType} ",
            "<if test='createId != null and createId!=\"\"'>" +
                    " and create_user_id =#{createId} ",
            "</if>" +
                    "<if test='company != null and company!=\"\"'>",
            " and (JSON_EXTRACT(justice_info, '$.resumeList[*].resumeOrg') LIKE '%${company}%'" +
                    " or company like '%${company}%'" +
                    " or justice_info->'$.orgName' LIKE '%${company}%')",
            "</if>",
            "<if test='post != null and post!=\"\"'>",
            " and (JSON_EXTRACT(justice_info, '$.resumeList[*].resumePos') LIKE '%${post}%'" +
                    " or post like '%${post}%'" +
                    " or justice_info->'$.pos' LIKE '%${post}%')",
            "</if>",
            "<if test='cities != null and cities.length>0'>" +
                    "and (" +
                    "<foreach collection='cities' item='id' open='(' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][2]','\"${id}\"') " +
                    "</foreach>",
            "<foreach collection='cities' item='id' open='or (' separator=' or ' close=')'>",
            " json_contains(justice_info ->'$.area[*][2]','\"${id}\"') " +
                    "</foreach>",
            "<foreach collection='cities' item='id' open='or (' separator=' or ' close=')'>",
            " json_contains(justice_info -> '$.resumeList[*].resumeOver[*][2]','\"${id}\"') " +
                    "</foreach>" +
                    ")",
            "</if>" +
                    " order BY crm_partner.update_time  DESC" +
                    "</script>"})
    List<Partner> findJusticeInfo(String createId, Integer partnerType, String company,
                                  String post, @Param("cities") String[] cities);

    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where crm_partner.disabled=0 " +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and create_user_id =#{createId} ",
            "</if>" +
                    "<if test='partnerType != null and partnerType!=\"\"'>" +
                    " and crm_partner.partner_type = #{partnerType}" +
                    "</if>" +
                    "<if test='bizListId != null and bizListId!=\"\"'>" +
                    " and json_contains(peer_info->'$.bizSList[*]','\"${bizListId}\"')" +
                    "</if>" +
                    "<if test='orgType != null and orgType!=\"\"'>" +
                    " and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>" +
                    "<if test='coopListId != null and coopListId!=\"\"'>" +
                    " and json_contains(peer_info->'$.coopSList[*]','\"${coopListId}\"') " +
                    "</if>" +

                    "<if test='company != null and company!=\"\"'>" +
                    " and crm_partner.company like '%${company}%' ",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    "</if>" +
                    " order BY crm_partner.update_time  DESC",
            "</script>",
    })
    List<Partner> findPeerInfo(String createId, String company, Integer partnerType, String orgType,
                               String bizListId, String coopListId
            , @Param("cities") String[] cities);

    @ResultMap(value = "partnerMap")
    @Select({"<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where crm_partner.disabled=0" +
                    "<if test='partnerType != null and partnerType!=\"\"'>" +
                    " and crm_partner.partner_type = #{partnerType}",
            "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and create_user_id =#{createId} ",
            "</if>" +
                    "<if test='company != null and company!=\"\"'>",
            " and (JSON_EXTRACT(org_info, '$.resumeList[*].resumeOrg') LIKE '%${company}%'" +
                    " or company like '%${company}%'" +
                    " or org_info->'$.name' LIKE '%${company}%')",
            "</if>",
            "<if test='post != null and post!=\"\"'>",
            " and (JSON_EXTRACT(org_info, '$.resumeList[*].resumePos') LIKE '%${post}%'" +
                    " or post like '%${post}%'" +
                    " or org_info->'$.pos' LIKE '%${post}%')",
            "</if>",
            "<if test='cities != null and cities.length>0'>" +
                    "and (" +
                    "<foreach collection='cities' item='id' open=' (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][2]','\"${id}\"') " +
                    "</foreach>",
            "<foreach collection='cities' item='id' open='or (' separator=' or ' close=')'>",
            " json_contains(org_info ->'$.relativeArea[*][2]','\"${id}\"') " +
                    "</foreach>",

            "<foreach collection='cities' item='id' open='or (' separator=' or ' close=')'>",
            " json_contains(org_info -> '$.resumeList[*].resumeOver[*][2]','\"${id}\"') " +
                    "</foreach>" +
                    ")",
            "</if>" +
                    " order BY crm_partner.update_time  DESC" +
                    "</script>"})
    List<Partner> findOrgInfo(String createId, Integer partnerType, String company,
                              String post, @Param("cities") String[] cities);
}
